package com.pm.config;

import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

@Component
public class DatabaseInitConfig implements CommandLineRunner {

    @Override
    public void run(String... args) throws Exception {
        String dbPath = "database/pm.db";
        String sqlFilePath = DatabaseInitConfig.class.getClassLoader().getResource("data.sql").getPath();
        
        // Check if database needs initialization
        if (needsInitialization(dbPath)) {
            System.out.println("Database not found or empty, initializing...");
            initializeDatabase(dbPath, sqlFilePath);
        } else {
            System.out.println("Database already exists, skipping initialization.");
        }
    }
    
    private boolean needsInitialization(String dbPath) {
        try {
            Path dbFile = Paths.get(dbPath);
            
            // If database file doesn't exist, needs initialization
            if (!Files.exists(dbFile)) {
                return true;
            }
            
            // Check if database has tables (not empty)
            Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
            Statement stmt = conn.createStatement();
            
            // Check if users table exists and has data
             try {
                 java.sql.ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
                 if (rs.next() && rs.getInt(1) > 0) {
                    stmt.close();
                    conn.close();
                    return false; // Database has data, no need to initialize
                }
            } catch (Exception e) {
                // Table doesn't exist, needs initialization
                stmt.close();
                conn.close();
                return true;
            }
            
            stmt.close();
            conn.close();
            return true; // Database exists but empty
            
        } catch (Exception e) {
            System.err.println("Error checking database: " + e.getMessage());
            return true; // If error, assume needs initialization
        }
    }
    
    private void initializeDatabase(String dbPath, String sqlFilePath) {
        try {
            // Read SQL file content
            // 判断是否为jar包中的路径
            String sqlContent;
            if (sqlFilePath.contains("!")) {
                // jar包中的路径格式为 file:/path/to/jar!/path/to/file
                String resourcePath = sqlFilePath.substring(sqlFilePath.indexOf("!") + 1);
                // 使用IOUtils将输入流转换为字符串
                InputStream inputStream = DatabaseInitConfig.class.getResourceAsStream(resourcePath);
                ByteArrayOutputStream result = new ByteArrayOutputStream();
                byte[] buffer = new byte[1024];
                int length;
                while ((length = inputStream.read(buffer)) != -1) {
                    result.write(buffer, 0, length);
                }
                sqlContent = result.toString("UTF-8");
                inputStream.close();
            } else {
                sqlContent = new String(Files.readAllBytes(Paths.get(sqlFilePath)));
            }
            
            // Connect to SQLite database
            Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
            Statement stmt = conn.createStatement();
            
            // Remove comments and split SQL statements properly
            String[] lines = sqlContent.split("\n");
            StringBuilder currentStatement = new StringBuilder();
            
            for (String line : lines) {
                line = line.trim();
                // Skip empty lines and comments
                if (line.isEmpty() || line.startsWith("--") || line.startsWith("SELECT 'Database")) {
                    continue;
                }
                
                currentStatement.append(line).append(" ");
                
                // If line ends with semicolon, execute the statement
                if (line.endsWith(";")) {
                    String sql = currentStatement.toString().trim();
                    if (!sql.isEmpty()) {
                        try {
                            stmt.execute(sql);
                            System.out.println("Executed: " + sql.substring(0, Math.min(80, sql.length())) + "...");
                        } catch (Exception e) {
                            System.err.println("Error executing: " + sql.substring(0, Math.min(80, sql.length())));
                            System.err.println("Error: " + e.getMessage());
                        }
                    }
                    currentStatement = new StringBuilder();
                }
            }
            
            stmt.close();
            conn.close();
            
            System.out.println("Database initialization completed successfully!");
            
        } catch (IOException e) {
            System.err.println("Error reading SQL file: " + e.getMessage());
        } catch (Exception e) {
            System.err.println("Database error: " + e.getMessage());
        }
    }
}